To Recipients | ${EMAIL_DESTINATION_ADDRESS} |
Cc Recipients | |
Subject | ${project_name} Flow Started |
Message | Project Name: ${project_name} Environment: -- Name: ${environment_name} -- Default schema: ${environment_default_schema} DW schema: ${schema_DW} STG schema: ${schema_STG} Steps: -- Truncate Stg: ${TruncateStg} Truncate DW: ${TruncateDW} -- Load Defaults: ${LoadDefaults} -- Load Staging: ${LoadStaging} Load Dimensions: ${LoadDimensions} Load Policy Transactions: ${LoadPolicyTransactions} Load Policy Summaries: ${LoadPolicySummaries} -- |
Sender Address | ${EMAIL_SENDER_ADDRESS} |
Reply Address | |
SMTP Username | ${EMAIL_AUTHENTICATION_USER} |
SMTP Password | ******** |
SMTP Hostname | ${EMAIL_SMTP_SERVER} |
SMTP Port | ${EMAIL_SMTP_PORT} |
Enable SSL/TLS | Yes |
Enable StartTLS | Yes |
To Recipients | ${EMAIL_DESTINATION_ADDRESS} |
Cc Recipients | |
Subject | ${project_name} Flow Complete |
Message | Project Name: ${project_name} Environment: -- Name: ${environment_name} -- Default schema: ${environment_default_schema} DW schema: ${schema_DW} STG schema: ${schema_STG} Steps: -- Truncate Stg: ${TruncateStg} Truncate DW: ${TruncateDW} -- Load Defaults: ${LoadDefaults} -- Load Staging: ${LoadStaging} Load Dimensions: ${LoadDimensions} Load Policy Transactions: ${LoadPolicyTransactions} Load Policy Summaries: ${LoadPolicySummaries} -- |
Sender Address | ${EMAIL_SENDER_ADDRESS} |
Reply Address | |
SMTP Username | ${EMAIL_AUTHENTICATION_USER} |
SMTP Password | ******** |
SMTP Hostname | ${EMAIL_SMTP_SERVER} |
SMTP Port | ${EMAIL_SMTP_PORT} |
Enable SSL/TLS | Yes |
Enable StartTLS | Yes |
To Recipients | ${EMAIL_DESTINATION_ADDRESS} |
Cc Recipients | |
Subject | ${project_name} Truncating |
Message | Project Name: ${project_name} Environment: -- Name: ${environment_name} -- Default schema: ${environment_default_schema} DW schema: ${schema_DW} STG schema: ${schema_STG} Steps: -- Truncate Stg: ${TruncateStg} Truncate DW: ${TruncateDW} -- Load Defaults: ${LoadDefaults} -- Load Staging: ${LoadStaging} Load Dimensions: ${LoadDimensions} Load Policy Transactions: ${LoadPolicyTransactions} Load Policy Summaries: ${LoadPolicySummaries} -- Truncating: ${Comment} |
Sender Address | ${EMAIL_SENDER_ADDRESS} |
Reply Address | |
SMTP Username | ${EMAIL_AUTHENTICATION_USER} |
SMTP Password | ******** |
SMTP Hostname | ${EMAIL_SMTP_SERVER} |
SMTP Port | ${EMAIL_SMTP_PORT} |
Enable SSL/TLS | Yes |
Enable StartTLS | Yes |
To Recipients | ${EMAIL_DESTINATION_ADDRESS} |
Cc Recipients | |
Subject | ${project_name} Load Defaults |
Message | Project Name: ${project_name} Environment: -- Name: ${environment_name} -- Default schema: ${environment_default_schema} DW schema: ${schema_DW} STG schema: ${schema_STG} Steps: -- Truncate Stg: ${TruncateStg} Truncate DW: ${TruncateDW} -- Load Defaults: ${LoadDefaults} -- Load Staging: ${LoadStaging} Load Dimensions: ${LoadDimensions} Load Policy Transactions: ${LoadPolicyTransactions} Load Policy Summaries: ${LoadPolicySummaries} -- |
Sender Address | ${EMAIL_SENDER_ADDRESS} |
Reply Address | |
SMTP Username | ${EMAIL_AUTHENTICATION_USER} |
SMTP Password | ******** |
SMTP Hostname | ${EMAIL_SMTP_SERVER} |
SMTP Port | ${EMAIL_SMTP_PORT} |
Enable SSL/TLS | Yes |
Enable StartTLS | Yes |
To Recipients | ${EMAIL_DESTINATION_ADDRESS} |
Cc Recipients | |
Subject | ${project_name} Load Complete |
Message | Loaddate is ${loaddate} SPINN EOD: ${is_SPINN_EOD_Complete} Project Name: ${project_name} Environment: -- Name: ${environment_name} -- Default schema: ${environment_default_schema} DW schema: ${schema_DW} STG schema: ${schema_STG} Steps: -- Truncate Stg: ${TruncateStg} Truncate DW: ${TruncateDW} -- Load Defaults: ${LoadDefaults} -- Load Staging: ${LoadStaging} Load Dimensions: ${LoadDimensions} Load Policy Transactions: ${LoadPolicyTransactions} Load Policy Summaries: ${LoadPolicySummaries} -- -- |
Sender Address | ${EMAIL_SENDER_ADDRESS} |
Reply Address | |
SMTP Username | ${EMAIL_AUTHENTICATION_USER} |
SMTP Password | ******** |
SMTP Hostname | ${EMAIL_SMTP_SERVER} |
SMTP Port | ${EMAIL_SMTP_PORT} |
Enable SSL/TLS | Yes |
Enable StartTLS | Yes |
Basic / Advanced | Advanced |
SQL Query | /* Note: this query will be run as a subquery for the purposes of sampling the data. As such it will be restricted by any subquery limitations specified by the current platform. */ select max(prev_loaddate) prev_loaddate from ( select max(bookdate) prev_loaddate from ${schema_STG}.etl_log union all select to_date('1900-01-01','yyyy-mm-dd') prev_loaddate ) data |
Scalar Variable Mapping | prev_loaddate, prev_loaddate |
Basic / Advanced | Advanced |
SQL Query | /* Note: this query will be run as a subquery for the purposes of sampling the data. As such it will be restricted by any subquery limitations specified by the current platform. */ with data as ( select distinct 1 JobDone from etl.LogsStatus where Updatetimestamp>coalesce(cast('${prev_loaddate}' as datetime),'1900-01-01') and JobStatusDaily='Completed' and JobStatusIncrement='Completed' union all select 0 JobDone ) select max(JobDone) JobDone from data |
Scalar Variable Mapping | is_SPINN_EOD_Complete, jobdone |
Basic / Advanced | Advanced |
SQL Query | /* Note: this query will be run as a subquery for the purposes of sampling the data. As such it will be restricted by any subquery limitations specified by the current platform. */ /*It's important to convert into PST because SPInn log time is in PST and use US/Pacific to account for Daylight Saving Time*/ select case when '${loaddate}'=to_date('1900-01-01','yyyy-mm-dd') then convert_timezone('UTC','US/Pacific', GetDate()) else '${loaddate}' end LoadDate |
Scalar Variable Mapping | loaddate, loaddate |
Basic / Advanced | Advanced |
SQL Query | /* Note: this query will be run as a subquery for the purposes of sampling the data. As such it will be restricted by any subquery limitations specified by the current platform. */ /*It's important to convert into PST because SPInn log time is in PST and use US/Pacific to account for Daylight Saving Time*/ /*----------------------------------------------------*/ /*BookDt > sql_bookDate and BookDt <= sql_currentDate */ /*----------------------------------------------------*/ select max(sql_bookDate) sql_bookDate from ( select case when '${sql_bookDate}'=to_date('1900-01-01','yyyy-mm-dd') then --if not set in variables then the latest loaded from the log bookdate else '${sql_bookDate}' end sql_bookDate from ${schema_STG}.etl_log union all select '${sql_bookDate}' sql_bookDate ) data |
Scalar Variable Mapping | sql_bookDate, sql_bookDate |
Basic / Advanced | Advanced |
SQL Query | /* Note: this query will be run as a subquery for the purposes of sampling the data. As such it will be restricted by any subquery limitations specified by the current platform. */ /*It's important to convert into PST because SPInn log time is in PST and use US/Pacific to account for Daylight Saving Time*/ /*----------------------------------------------------*/ /*BookDt > sql_bookDate and BookDt <= sql_currentDate */ /*----------------------------------------------------*/ select case when '${sql_currentDate}'=to_date('1900-01-01','yyyy-mm-dd') then /*if sql_currentDate is not set then Yesterday*/ dateadd(day,-1,convert_timezone('UTC','US/Pacific', GetDate()) ) else '${sql_currentDate}' end sql_currentDate |
Scalar Variable Mapping | sql_currentDate, sql_currentDate |
SQL Script | /*----------------------------------------------------*/ /*BookDt > sql_bookDate and BookDt <= sql_currentDate */ /* Acording to thie condition in every day load */ /* YESTERDAY bookdate is in sql_currentDate */ /*----------------------------------------------------*/ insert into ${schema_STG}.etl_log (loaddate, bookdate,loadstarttime,loadcomments) values ('${loaddate}', '${sql_currentDate}',GetDate(),'${sql_bookDate}'); |
To Recipients | ${EMAIL_DESTINATION_ADDRESS} |
Cc Recipients | |
Subject | ${project_name} Data Load Started |
Message | Loaddate is ${loaddate} SPINN EOD: ${is_SPINN_EOD_Complete} Project Name: ${project_name} Environment: -- Name: ${environment_name} -- Default schema: ${environment_default_schema} DW schema: ${schema_DW} STG schema: ${schema_STG} Steps: -- Truncate Stg: ${TruncateStg} Truncate DW: ${TruncateDW} -- Load Defaults: ${LoadDefaults} -- Load Staging: ${LoadStaging} Load Dimensions: ${LoadDimensions} Load Policy Transactions: ${LoadPolicyTransactions} Load Policy Summaries: ${LoadPolicySummaries} -- |
Sender Address | ${EMAIL_SENDER_ADDRESS} |
Reply Address | |
SMTP Username | ${EMAIL_AUTHENTICATION_USER} |
SMTP Password | ******** |
SMTP Hostname | ${EMAIL_SMTP_SERVER} |
SMTP Port | ${EMAIL_SMTP_PORT} |
Enable SSL/TLS | Yes |
Enable StartTLS | Yes |
To Recipients | ${EMAIL_DESTINATION_ADDRESS} |
Cc Recipients | |
Subject | ${project_name} Data Load did NOT Start |
Message | SPInn EOD not complete or complete with errors SPINN EOD: ${is_SPINN_EOD_Complete} Project Name: ${project_name} Environment: -- Name: ${environment_name} -- Default schema: ${environment_default_schema} DW schema: ${schema_DW} STG schema: ${schema_STG} Steps: -- Truncate Stg: ${TruncateStg} Truncate Dims: ${TruncateDims} -- Load Defaults: ${LoadDefaults} -- Load Staging: ${LoadStaging} Load Dimensions: ${LoadDimensions} Load Policy Transactions: ${LoadPolicyTransactions} Load Policy Summaries: ${LoadPolicySummaries} -- |
Sender Address | ${EMAIL_SENDER_ADDRESS} |
Reply Address | |
SMTP Username | ${EMAIL_AUTHENTICATION_USER} |
SMTP Password | ******** |
SMTP Hostname | ${EMAIL_SMTP_SERVER} |
SMTP Port | ${EMAIL_SMTP_PORT} |
Enable SSL/TLS | Yes |
Enable StartTLS | Yes |
To Recipients | ${EMAIL_DESTINATION_ADDRESS} |
Cc Recipients | |
Subject | ${project_name} Load of Staging Started |
Message | Load of Staging Started |
Sender Address | ${EMAIL_SENDER_ADDRESS} |
Reply Address | |
SMTP Username | ${EMAIL_AUTHENTICATION_USER} |
SMTP Password | ******** |
SMTP Hostname | ${EMAIL_SMTP_SERVER} |
SMTP Port | ${EMAIL_SMTP_PORT} |
Enable SSL/TLS | Yes |
Enable StartTLS | Yes |
Basic / Advanced | Advanced |
SQL Query | select distinct cast(to_char(accountingdt,'yyyymm') as int) month_id from kdlab.stg_policytransaction where cast(to_char(bookdt,'yyyymm') as int)>=cast(to_char(accountingdt,'yyyymm') as int) /*no future months, limit to the current and previous months*/ |
Grid Variable | Months |
Grid Variable Mapping | month_id |
To Recipients | ${EMAIL_DESTINATION_ADDRESS} |
Cc Recipients | |
Subject | ${project_name} Load of Dimensions Started |
Message | Load of Dimensions Started |
Sender Address | ${EMAIL_SENDER_ADDRESS} |
Reply Address | |
SMTP Username | ${EMAIL_AUTHENTICATION_USER} |
SMTP Password | ******** |
SMTP Hostname | ${EMAIL_SMTP_SERVER} |
SMTP Port | ${EMAIL_SMTP_PORT} |
Enable SSL/TLS | Yes |
Enable StartTLS | Yes |
To Recipients | ${EMAIL_DESTINATION_ADDRESS} |
Cc Recipients | |
Subject | ${project_name} Load of Fact Policy Transaction Started |
Message | Load of Fact Policy Transaction Started |
Sender Address | ${EMAIL_SENDER_ADDRESS} |
Reply Address | |
SMTP Username | ${EMAIL_AUTHENTICATION_USER} |
SMTP Password | ******** |
SMTP Hostname | ${EMAIL_SMTP_SERVER} |
SMTP Port | ${EMAIL_SMTP_PORT} |
Enable SSL/TLS | Yes |
Enable StartTLS | Yes |
To Recipients | ${EMAIL_DESTINATION_ADDRESS} |
Cc Recipients | |
Subject | ${project_name} Load of Fact Policy Summaries Started |
Message | Load of Fact Policy Summaries Started |
Sender Address | ${EMAIL_SENDER_ADDRESS} |
Reply Address | |
SMTP Username | ${EMAIL_AUTHENTICATION_USER} |
SMTP Password | ******** |
SMTP Hostname | ${EMAIL_SMTP_SERVER} |
SMTP Port | ${EMAIL_SMTP_PORT} |
Enable SSL/TLS | Yes |
Enable StartTLS | Yes |
Schema | ${schema_DW} |
Target Table Name | dim_application |
Fix Data Type Mismatches | No |
Column Mapping | loaddate, loaddate, policy_id, policy_id, systemid, systemid, currentflg, currentflg, bookdt, bookdt, transactioneffectivedt, transactioneffectivedt, policy_uniqueid, policy_uniqueid, transactioncd, transactioncd, policynumber, policynumber, term, term, effectivedate, effectivedate, expirationdate, expirationdate, carriercd, carriercd, companycd, companycd, termdays, termdays, carriergroupcd, carriergroupcd, statecd, statecd, businesssourcecd, businesssourcecd, previouscarriercd, previouscarriercd, policyformcode, policyformcode, subtypecd, subtypecd, payplancd, payplancd, inceptiondt, inceptiondt, priorpolicynumber, priorpolicynumber, previouspolicynumber, previouspolicynumber, affinitygroupcd, affinitygroupcd, programind, programind, relatedpolicynumber, relatedpolicynumber, twopaydiscountind, twopaydiscountind, quotenumber, quotenumber, renewaltermcd, renewaltermcd, rewritepolicyref, rewritepolicyref, rewritefrompolicyref, rewritefrompolicyref, canceldt, canceldt, reinstatedt, reinstatedt, persistencydiscountdt, persistencydiscountdt, paperlessdelivery, paperlessdelivery, multicardiscountind, multicardiscountind, latefee, latefee, nsffee, nsffee, installmentfee, installmentfee, batchquotesourcecd, batchquotesourcecd, waivepolicyfeeind, waivepolicyfeeind, liabilitylimitcpl, liabilitylimitcpl, liabilityreductionind, liabilityreductionind, liabilitylimitolt, liabilitylimitolt, personalliabilitylimit, personalliabilitylimit, gloccurrencelimit, gloccurrencelimit, glaggregatelimit, glaggregatelimit, policy_spinn_status, policy_spinn_status, bilimit, bilimit, pdlimit, pdlimit, umbilimit, umbilimit, medpaylimit, medpaylimit, multipolicydiscount, multipolicydiscount, multipolicyautodiscount, multipolicyautodiscount, multipolicyautonumber, multipolicyautonumber, multipolicyhomediscount, multipolicyhomediscount, homerelatedpolicynumber, homerelatedpolicynumber, multipolicyumbrelladiscount, multipolicyumbrelladiscount, umbrellarelatedpolicynumber, umbrellarelatedpolicynumber, cseemployeediscountind, cseemployeediscountind, fullpaydiscountind, fullpaydiscountind, primarypolicynumber, primarypolicynumber, landlordind, landlordind, personalinjuryind, personalinjuryind, vehiclelistconfirmedind, vehiclelistconfirmedind, altsubtypecd, altsubtypecd, firstpayment, firstpayment, lastpayment, lastpayment, balanceamt, balanceamt, paidamt, paidamt, product_uniqueid, product_uniqueid, company_uniqueid, company_uniqueid, producer_uniqueid, producer_uniqueid, firstinsured_uniqueid, firstinsured_uniqueid, accountref, accountref, customer_uniqueid, customer_uniqueid, mgafeeplancd, mgafeeplancd, mgafeepct, mgafeepct, tpafeeplancd, tpafeeplancd, tpafeepct, tpafeepct, applicationnumber, applicationnumber, application_updatetimestamp, application_updatetimestamp, quoteinfo_updatedt, quoteinfo_updatedt, quoteinfo_adduser_uniqueid, quoteinfo_adduser_uniqueid, original_policy_uniqueid, original_policy_uniqueid, application_type, application_type, quoteinfo_type, quoteinfo_type, application_status, application_status, quoteinfo_status, quoteinfo_status, quoteinfo_closereasoncd, quoteinfo_closereasoncd, quoteinfo_closesubreasoncd, quoteinfo_closesubreasoncd, quoteinfo_closecomment, quoteinfo_closecomment, writtenpremiumamt, writtenpremiumamt, fulltermamt, fulltermamt, commissionamt, commissionamt |
Unique Keys | systemid |
Update Strategy | Update/Insert |
Schema | ${schema_DW} |
Target Table Name | dim_driver |
Fix Data Type Mismatches | No |
Column Mapping | loaddate, loaddate, policy_id, policy_id, systemid, systemid, currentflg, currentflg, bookdt, bookdt, transactioneffectivedt, transactioneffectivedt, policy_uniqueid, policy_uniqueid, drivernumber, drivernumber, driver_uniqueid, driver_uniqueid, spinndriver_id, spinndriver_id, status, status, firstname, firstname, lastname, lastname, licensenumber, licensenumber, licensedt, licensedt, driverinfocd, driverinfocd, drivertypecd, drivertypecd, driverstatuscd, driverstatuscd, licensedstateprovcd, licensedstateprovcd, relationshiptoinsuredcd, relationshiptoinsuredcd, scholasticdiscountind, scholasticdiscountind, mvrrequestind, mvrrequestind, mvrstatus, mvrstatus, mvrstatusdt, mvrstatusdt, maturedriverind, maturedriverind, drivertrainingind, drivertrainingind, gooddriverind, gooddriverind, accidentpreventioncoursecompletiondt, accidentpreventioncoursecompletiondt, drivertrainingcompletiondt, drivertrainingcompletiondt, accidentpreventioncourseind, accidentpreventioncourseind, scholasticcertificationdt, scholasticcertificationdt, activemilitaryind, activemilitaryind, permanentlicenseind, permanentlicenseind, newtostateind, newtostateind, persontypecd, persontypecd, gendercd, gendercd, birthdt, birthdt, maritalstatuscd, maritalstatuscd, occupationclasscd, occupationclasscd, positiontitle, positiontitle, currentresidencecd, currentresidencecd, civilservantind, civilservantind, retiredind, retiredind, newteenexpirationdt, newteenexpirationdt, attachedvehicleref, attachedvehicleref, viol_pointschargedterm, viol_pointschargedterm, acci_pointschargedterm, acci_pointschargedterm, susp_pointschargedterm, susp_pointschargedterm, other_pointschargedterm, other_pointschargedterm, gooddriverpoints_chargedterm, gooddriverpoints_chargedterm, sr22feeind, sr22feeind, maturecertificationdt, maturecertificationdt, agefirstlicensed, agefirstlicensed |
Unique Keys | driver_uniqueid |
Update Strategy | Update/Insert |
Schema | ${schema_DW} |
Target Table Name | dim_insured |
Fix Data Type Mismatches | No |
Column Mapping | insured_id, insured_id, loaddate, loaddate, policy_id, policy_id, systemid, systemid, currentflg, currentflg, bookdt, bookdt, transactioneffectivedt, transactioneffectivedt, policy_uniqueid, policy_uniqueid, insured_uniqueid, insured_uniqueid, first_name, first_name, last_name, last_name, commercialname, commercialname, dob, dob, occupation, occupation, gender, gender, maritalstatus, maritalstatus, address1, address1, address2, address2, county, county, city, city, state, state, postalcode, postalcode, country, country, telephone, telephone, mobile, mobile, email, email, jobtitle, jobtitle, insurancescore, insurancescore, overriddeninsurancescore, overriddeninsurancescore, applieddt, applieddt, insurancescorevalue, insurancescorevalue, ratepageeffectivedt, ratepageeffectivedt, insscoretiervalueband, insscoretiervalueband, financialstabilitytier, financialstabilitytier |
Unique Keys | insured_id |
Update Strategy | Update/Insert |
Schema | ${schema_DW} |
Target Table Name | dim_product |
Fix Data Type Mismatches | No |
Column Mapping | loaddate, loaddate, product_uniqueid, product_uniqueid, carriergroupcd, carriergroupcd, description, description, subtypecd, subtypecd, productversion, productversion, name, name, producttypecd, producttypecd, carriercd, carriercd, isselect, isselect, linecd, linecd, altsubtypecd, altsubtypecd, subtypeshortdesc, subtypeshortdesc, subtypefulldesc, subtypefulldesc, policynumberprefix, policynumberprefix, startdt, startdt, stopdt, stopdt, renewalstartdt, renewalstartdt, renewalstopdt, renewalstopdt, statecd, statecd, contract, contract, lob, lob, propertyform, propertyform, prerenewaldays, prerenewaldays, autorenewaldays, autorenewaldays, mgafeeplancd, mgafeeplancd, tpafeeplancd, tpafeeplancd |
Unique Keys | product_uniqueid |
Update Strategy | Update/Insert |
SQL Query | with stg_query as(select Building_Uniqueid coveredrisk_uniqueid ,LOADDATE ,SystemId ,BookDt ,TransactionEffectiveDt ,case when Status='Deleted' then 1 else 0 end deleted_indicator ,BldgNumber Risk_Number ,Risk_UniqueId ,Policy_Uniqueid ,isnull(Risk_Type,'~') Risk_Type from ${schema_STG}.STG_BUILDING union all select Vehicle_Uniqueid coveredrisk_uniqueid ,LOADDATE ,SystemId ,BookDt ,TransactionEffectiveDt ,case when Status='Deleted' then 1 else 0 end deleted_indicator ,VehNumber Risk_Number ,Risk_UniqueId ,Policy_Uniqueid ,isnull(Risk_Type,'~') Risk_Type from ${schema_STG}.STG_VEHICLE) ,data as ( select row_number()over (partition by coveredrisk_uniqueid order by bookdt) rn, * from stg_query ) select Policy_Uniqueid Policy_Id ,0 CurrentFlg ,* from data where rn=1 |
Schema | ${schema_DW} |
Target Table Name | dim_coveredrisk |
Fix Data Type Mismatches | No |
Column Mapping | LOADDATE, LOADDATE, POLICY_ID, POLICY_ID, SystemId, SystemId, CurrentFlg, CurrentFlg, BookDt, BookDt, TransactionEffectiveDt, TransactionEffectiveDt, POLICY_UNIQUEID, POLICY_UNIQUEID, deleted_indicator, deleted_indicator, risk_number, risk_number, risk_type, risk_type, RISK_UNIQUEID, RISK_UNIQUEID, COVEREDRISK_UNIQUEID, COVEREDRISK_UNIQUEID |
Unique Keys | coveredrisk_uniqueid |
Update Strategy | Update/Insert |
SQL Query | with data as ( SELECT ROW_NUMBER() OVER(partition by cr.coveredrisk_id order by stg.BookDt) rn, cr.coveredrisk_id as vehicle_id, stg.* FROM STG_VEHICLE stg join ${schema_STG}.DIM_COVEREDRISK cr on stg.Vehicle_Uniqueid=cr.coveredrisk_uniqueid and stg.policy_uniqueid=cr.policy_id and stg.SystemId=cr.SystemId ) select policy_uniqueid as policy_id, 0 CurrentFlg, * from data where rn=1 |
Schema | ${schema_DW} |
Target Table Name | dim_vehicle |
Fix Data Type Mismatches | No |
Column Mapping | vehicle_id, vehicle_id, loaddate, loaddate, policy_id, policy_id, systemid, systemid, currentflg, currentflg, bookdt, bookdt, transactioneffectivedt, transactioneffectivedt, policy_uniqueid, policy_uniqueid, risk_uniqueid, risk_uniqueid, vehnumber, vehnumber, vehicle_uniqueid, vehicle_uniqueid, spinnvehicle_id, spinnvehicle_id, status, status, stateprovcd, stateprovcd, county, county, postalcode, postalcode, city, city, addr1, addr1, addr2, addr2, garagaddrflg, garagaddrflg, latitude, latitude, longitude, longitude, garagpostalcode, garagpostalcode, garagpostalcodeflg, garagpostalcodeflg, manufacturer, manufacturer, model, model, modelyr, modelyr, vehidentificationnumber, vehidentificationnumber, validvinind, validvinind, vehlicensenumber, vehlicensenumber, registrationstateprovcd, registrationstateprovcd, vehbodytypecd, vehbodytypecd, performancecd, performancecd, restraintcd, restraintcd, antibrakingsystemcd, antibrakingsystemcd, antitheftcd, antitheftcd, enginesize, enginesize, enginecylinders, enginecylinders, enginehorsepower, enginehorsepower, enginetype, enginetype, vehusecd, vehusecd, garageterritory, garageterritory, collisionded, collisionded, comprehensiveded, comprehensiveded, statedamt, statedamt, classcd, classcd, ratingvalue, ratingvalue, costnewamt, costnewamt, estimatedannualdistance, estimatedannualdistance, estimatedworkdistance, estimatedworkdistance, leasedvehind, leasedvehind, purchasedt, purchasedt, statedamtind, statedamtind, neworusedind, neworusedind, carpoolind, carpoolind, odometerreading, odometerreading, weekspermonthdriven, weekspermonthdriven, daylightrunninglightsind, daylightrunninglightsind, passiveseatbeltind, passiveseatbeltind, daysperweekdriven, daysperweekdriven, umpdlimit, umpdlimit, towingandlaborind, towingandlaborind, rentalreimbursementind, rentalreimbursementind, liabilitywaiveind, liabilitywaiveind, ratefeesind, ratefeesind, optionalequipmentvalue, optionalequipmentvalue, customizingequipmentind, customizingequipmentind, customizingequipmentdesc, customizingequipmentdesc, invalidvinacknowledgementind, invalidvinacknowledgementind, ignoreumpdwcdind, ignoreumpdwcdind, recalculateratingsymbolind, recalculateratingsymbolind, programtypecd, programtypecd, cmpratingvalue, cmpratingvalue, colratingvalue, colratingvalue, liabilityratingvalue, liabilityratingvalue, medpayratingvalue, medpayratingvalue, racmpratingvalue, racmpratingvalue, racolratingvalue, racolratingvalue, rabiratingsymbol, rabiratingsymbol, rapdratingsymbol, rapdratingsymbol, ramedpayratingsymbol, ramedpayratingsymbol, estimatedannualdistanceoverride, estimatedannualdistanceoverride, originalestimatedannualmiles, originalestimatedannualmiles, reportedmileagenonsave, reportedmileagenonsave, mileage, mileage, estimatednoncommutemiles, estimatednoncommutemiles, titlehistoryissue, titlehistoryissue, odometerproblems, odometerproblems, bundle, bundle, loanleasegap, loanleasegap, equivalentreplacementcost, equivalentreplacementcost, originalequipmentmanufacturer, originalequipmentmanufacturer, optionalrideshare, optionalrideshare, medicalpartsaccessibility, medicalpartsaccessibility, odometerreadingprior, odometerreadingprior, reportedmileagenonsavedtprior, reportedmileagenonsavedtprior, fullglasscovind, fullglasscovind, boatlengthfeet, boatlengthfeet, motorhorsepower, motorhorsepower, replacementof, replacementof, reportedmileagenonsavedt, reportedmileagenonsavedt, manufacturersymbol, manufacturersymbol, modelsymbol, modelsymbol, bodystylesymbol, bodystylesymbol, symbolcode, symbolcode, verifiedmileageoverride, verifiedmileageoverride |
Unique Keys | vehicle_id |
Update Strategy | Update/Insert |
SQL Query | with data as ( select row_number()over (partition by cr.coveredrisk_id order by stg.bookdt) rn, cr.coveredrisk_id as building_id, stg.* from ${schema_STG}.stg_building stg join ${schema_STG}.DIM_COVEREDRISK cr on stg.Building_Uniqueid=cr.coveredrisk_uniqueid and stg.policy_uniqueid=cr.policy_id and stg.SystemId=cr.SystemId ) select policy_uniqueid as policy_id , 0 CurrentFlg , * from data where rn=1 |
Schema | ${schema_DW} |
Target Table Name | dim_building |
Fix Data Type Mismatches | No |
Column Mapping | building_id, building_id, loaddate, loaddate, policy_id, policy_id, systemid, systemid, currentflg, currentflg, bookdt, bookdt, transactioneffectivedt, transactioneffectivedt, policy_uniqueid, policy_uniqueid, risk_uniqueid, risk_uniqueid, bldgnumber, bldgnumber, building_uniqueid, building_uniqueid, spinnbuilding_id, spinnbuilding_id, status, status, stateprovcd, stateprovcd, county, county, postalcode, postalcode, city, city, addr1, addr1, addr2, addr2, businesscategory, businesscategory, businessclass, businessclass, constructioncd, constructioncd, roofcd, roofcd, yearbuilt, yearbuilt, sqft, sqft, stories, stories, units, units, occupancycd, occupancycd, protectionclass, protectionclass, territorycd, territorycd, buildinglimit, buildinglimit, contentslimit, contentslimit, valuationmethod, valuationmethod, inflationguardpct, inflationguardpct, ordinanceorlawind, ordinanceorlawind, scheduledpremiummod, scheduledpremiummod, windhailexclusion, windhailexclusion, covalimit, covalimit, covblimit, covblimit, covclimit, covclimit, covdlimit, covdlimit, covelimit, covelimit, covflimit, covflimit, allperilded, allperilded, burglaryalarmtype, burglaryalarmtype, firealarmtype, firealarmtype, covblimitincluded, covblimitincluded, covblimitincrease, covblimitincrease, covclimitincluded, covclimitincluded, covclimitincrease, covclimitincrease, covdlimitincluded, covdlimitincluded, covdlimitincrease, covdlimitincrease, ordinanceorlawpct, ordinanceorlawpct, neighborhoodcrimewatchind, neighborhoodcrimewatchind, employeecreditind, employeecreditind, multipolicyind, multipolicyind, homewarrantycreditind, homewarrantycreditind, yearoccupied, yearoccupied, yearpurchased, yearpurchased, typeofstructure, typeofstructure, feettofirehydrant, feettofirehydrant, numberoffamilies, numberoffamilies, milesfromfirestation, milesfromfirestation, rooms, rooms, roofpitch, roofpitch, firedistrict, firedistrict, sprinklersystem, sprinklersystem, fireextinguisherind, fireextinguisherind, kitchenfireextinguisherind, kitchenfireextinguisherind, deadboltind, deadboltind, gatedcommunityind, gatedcommunityind, centralheatingind, centralheatingind, foundation, foundation, wiringrenovation, wiringrenovation, wiringrenovationcompleteyear, wiringrenovationcompleteyear, plumbingrenovation, plumbingrenovation, heatingrenovation, heatingrenovation, plumbingrenovationcompleteyear, plumbingrenovationcompleteyear, exteriorpaintrenovation, exteriorpaintrenovation, heatingrenovationcompleteyear, heatingrenovationcompleteyear, circuitbreakersind, circuitbreakersind, copperwiringind, copperwiringind, exteriorpaintrenovationcompleteyear, exteriorpaintrenovationcompleteyear, copperpipesind, copperpipesind, earthquakeretrofitind, earthquakeretrofitind, primaryfuelsource, primaryfuelsource, secondaryfuelsource, secondaryfuelsource, usagetype, usagetype, homegardcreditind, homegardcreditind, multipolicynumber, multipolicynumber, localfirealarmind, localfirealarmind, numlosses, numlosses, covalimitincrease, covalimitincrease, covalimitincluded, covalimitincluded, monthsrentedout, monthsrentedout, roofreplacement, roofreplacement, safeguardplusind, safeguardplusind, covelimitincluded, covelimitincluded, roofreplacementcompleteyear, roofreplacementcompleteyear, covelimitincrease, covelimitincrease, owneroccupiedunits, owneroccupiedunits, tenantoccupiedunits, tenantoccupiedunits, replacementcostdwellingind, replacementcostdwellingind, feettopropertyline, feettopropertyline, galvanizedpipeind, galvanizedpipeind, workerscompinservant, workerscompinservant, workerscompoutservant, workerscompoutservant, liabilityterritorycd, liabilityterritorycd, premisesliabilitymedpayind, premisesliabilitymedpayind, relatedprivatestructureexclusion, relatedprivatestructureexclusion, vandalismexclusion, vandalismexclusion, vandalismind, vandalismind, roofexclusion, roofexclusion, expandedreplacementcostind, expandedreplacementcostind, replacementvalueind, replacementvalueind, otherpolicynumber1, otherpolicynumber1, otherpolicynumber2, otherpolicynumber2, otherpolicynumber3, otherpolicynumber3, primarypolicynumber, primarypolicynumber, otherpolicynumbers, otherpolicynumbers, reportedfirehazardscore, reportedfirehazardscore, firehazardscore, firehazardscore, reportedsteepslopeind, reportedsteepslopeind, steepslopeind, steepslopeind, reportedhomereplacementcost, reportedhomereplacementcost, reportedprotectionclass, reportedprotectionclass, earthquakezone, earthquakezone, mmiscore, mmiscore, homeinspectiondiscountind, homeinspectiondiscountind, ratingtier, ratingtier, soiltypecd, soiltypecd, reportedfirelineassessment, reportedfirelineassessment, aaisfireprotectionclass, aaisfireprotectionclass, inspectionscore, inspectionscore, annualrents, annualrents, pitchofroof, pitchofroof, totallivingsqft, totallivingsqft, parkingsqft, parkingsqft, parkingtype, parkingtype, retrofitcompleted, retrofitcompleted, numpools, numpools, fullyfenced, fullyfenced, divingboard, divingboard, gym, gym, freeweights, freeweights, wirefencing, wirefencing, otherrecreational, otherrecreational, otherrecreationaldesc, otherrecreationaldesc, healthinspection, healthinspection, healthinspectiondt, healthinspectiondt, healthinspectioncited, healthinspectioncited, priordefectrepairs, priordefectrepairs, msbreconstructionestimate, msbreconstructionestimate, biindemnityperiod, biindemnityperiod, equipmentbreakdown, equipmentbreakdown, moneysecurityonpremises, moneysecurityonpremises, moneysecurityoffpremises, moneysecurityoffpremises, waterbackupsump, waterbackupsump, sprinkleredbuildings, sprinkleredbuildings, surveillancecams, surveillancecams, gatedcomplexkeyaccess, gatedcomplexkeyaccess, eqretrofit, eqretrofit, unitsperbuilding, unitsperbuilding, numstories, numstories, constructionquality, constructionquality, burglaryrobbery, burglaryrobbery, nfpaclassification, nfpaclassification, areasofcoverage, areasofcoverage, codetector, codetector, smokedetector, smokedetector, smokedetectorinspectind, smokedetectorinspectind, waterheatersecured, waterheatersecured, boltedorsecured, boltedorsecured, softstorycripple, softstorycripple, seniorhousingpct, seniorhousingpct, designatedseniorhousing, designatedseniorhousing, studenthousingpct, studenthousingpct, designatedstudenthousing, designatedstudenthousing, priorlosses, priorlosses, tenantevictions, tenantevictions, vacancyrateexceed, vacancyrateexceed, seasonalrentals, seasonalrentals, condoinsuingagmt, condoinsuingagmt, gasvalve, gasvalve, owneroccupiedpct, owneroccupiedpct, restaurantname, restaurantname, hoursofoperation, hoursofoperation, restaurantsqft, restaurantsqft, seatingcapacity, seatingcapacity, annualgrosssales, annualgrosssales, seasonalorclosed, seasonalorclosed, barcocktaillounge, barcocktaillounge, liveentertainment, liveentertainment, beerwinegrosssales, beerwinegrosssales, distilledspiritsserved, distilledspiritsserved, kitchendeepfryer, kitchendeepfryer, solidfuelcooking, solidfuelcooking, ansulsystem, ansulsystem, ansulannualinspection, ansulannualinspection, tenantnameslist, tenantnameslist, tenantbusinesstype, tenantbusinesstype, tenantglliability, tenantglliability, insuredoccupiedportion, insuredoccupiedportion, valetparking, valetparking, lessorsqft, lessorsqft, buildingrisknumber, buildingrisknumber, multipolicyindumbrella, multipolicyindumbrella, poolind, poolind, studsuprenovation, studsuprenovation, studsuprenovationcompleteyear, studsuprenovationcompleteyear, multipolicynumberumbrella, multipolicynumberumbrella, rctmsbamt, rctmsbamt, rctmsbhomestyle, rctmsbhomestyle, winsoverridenonsmokerdiscount, winsoverridenonsmokerdiscount, winsoverrideseniordiscount, winsoverrideseniordiscount, itv, itv, itvdate, itvdate, msbreporttype, msbreporttype, vandalismdesiredind, vandalismdesiredind, woodshakesiding, woodshakesiding, cseagent, cseagent, propertymanager, propertymanager, rentersinsurance, rentersinsurance, waterdetectiondevice, waterdetectiondevice, autohomeind, autohomeind, earthquakeumbrellaind, earthquakeumbrellaind, landlordind, landlordind, lossassessment, lossassessment, gasshutoffind, gasshutoffind, waterded, waterded, serviceline, serviceline, functionalreplacementcost, functionalreplacementcost, milesofstreet, milesofstreet, hoaexteriorstructure, hoaexteriorstructure, retailportiondevelopment, retailportiondevelopment, lightindustrialtype, lightindustrialtype, lightindustrialdescription, lightindustrialdescription, poolcoveragelimit, poolcoveragelimit, multifamilyresidentialbuildings, multifamilyresidentialbuildings, singlefamilydwellings, singlefamilydwellings, annualpayroll, annualpayroll, annualrevenue, annualrevenue, bedsoccupied, bedsoccupied, emergencylighting, emergencylighting, exitsignsposted, exitsignsposted, fulltimestaff, fulltimestaff, licensedbeds, licensedbeds, numberoffireextinguishers, numberoffireextinguishers, otherfireextinguishers, otherfireextinguishers, oxygentanks, oxygentanks, parttimestaff, parttimestaff, smokingpermitted, smokingpermitted, staffonduty, staffonduty, typeoffireextinguishers, typeoffireextinguishers, covaddrr_secondaryresidence, covaddrr_secondaryresidence, covaddrrprem_secondaryresidence, covaddrrprem_secondaryresidence, hodeluxe, hodeluxe, latitude, latitude, longitude, longitude, linecd, linecd, wuiclass, wuiclass, censusblock, censusblock, waterriskscore, waterriskscore, landlordlosspreventionservices, landlordlosspreventionservices, enhancedwatercoverage, enhancedwatercoverage, landlordproperty, landlordproperty, liabilityextendedtoothers, liabilityextendedtoothers, lossofuseextendedtime, lossofuseextendedtime, onpremisestheft, onpremisestheft, bedbugmitigation, bedbugmitigation, habitabilityexclusion, habitabilityexclusion, wildfirehazardpotential, wildfirehazardpotential, backupofsewersanddrains, backupofsewersanddrains, vegetationsetbackft, vegetationsetbackft, yarddebriscoveragearea, yarddebriscoveragearea, yarddebriscoveragepercentage, yarddebriscoveragepercentage, capetrampoline, capetrampoline, capepool, capepool, roofconditionrating, roofconditionrating, trampolineind, trampolineind, plumbingmaterial, plumbingmaterial, centralizedheating, centralizedheating, firedistrictsubscriptioncode, firedistrictsubscriptioncode, roofcondition, roofcondition |
Unique Keys | building_id |
Update Strategy | Update/Insert |
SQL Query | with data as ( select row_number()over (partition by cr.coveredrisk_id order by stg.bookdt) rn, cr.coveredrisk_id, stg.policy_uniqueid as policy_id, 0 CurrentFlg, stg.* from ${schema_STG}.stg_risk_coverage stg join ${schema_STG}.dim_coveredrisk cr on stg.Risk_Uniqueid=cr.risk_uniqueid and stg.policy_uniqueid=cr.policy_id and stg.SystemId=cr.SystemId ) select * from data where rn=1 |
Schema | ${schema_DW} |
Target Table Name | dim_risk_coverage |
Fix Data Type Mismatches | No |
Column Mapping | coveredrisk_id, coveredrisk_id, loaddate, loaddate, policy_id, policy_id, systemid, systemid, currentflg, currentflg, bookdt, bookdt, transactioneffectivedt, transactioneffectivedt, policy_uniqueid, policy_uniqueid, risk_uniqueid, risk_uniqueid, cova_limit1, cova_limit1, cova_limit2, cova_limit2, cova_deductible1, cova_deductible1, cova_deductible2, cova_deductible2, cova_fulltermamt, cova_fulltermamt, covb_limit1, covb_limit1, covb_limit2, covb_limit2, covb_deductible1, covb_deductible1, covb_deductible2, covb_deductible2, covb_fulltermamt, covb_fulltermamt, covc_limit1, covc_limit1, covc_limit2, covc_limit2, covc_deductible1, covc_deductible1, covc_deductible2, covc_deductible2, covc_fulltermamt, covc_fulltermamt, covd_limit1, covd_limit1, covd_limit2, covd_limit2, covd_deductible1, covd_deductible1, covd_deductible2, covd_deductible2, covd_fulltermamt, covd_fulltermamt, cove_limit1, cove_limit1, cove_limit2, cove_limit2, cove_deductible1, cove_deductible1, cove_deductible2, cove_deductible2, cove_fulltermamt, cove_fulltermamt, bedbug_limit1, bedbug_limit1, bedbug_limit2, bedbug_limit2, bedbug_deductible1, bedbug_deductible1, bedbug_deductible2, bedbug_deductible2, bedbug_fulltermamt, bedbug_fulltermamt, bolaw_limit1, bolaw_limit1, bolaw_limit2, bolaw_limit2, bolaw_deductible1, bolaw_deductible1, bolaw_deductible2, bolaw_deductible2, bolaw_fulltermamt, bolaw_fulltermamt, coc_limit1, coc_limit1, coc_limit2, coc_limit2, coc_deductible1, coc_deductible1, coc_deductible2, coc_deductible2, coc_fulltermamt, coc_fulltermamt, eqpbk_limit1, eqpbk_limit1, eqpbk_limit2, eqpbk_limit2, eqpbk_deductible1, eqpbk_deductible1, eqpbk_deductible2, eqpbk_deductible2, eqpbk_fulltermamt, eqpbk_fulltermamt, fraud_limit1, fraud_limit1, fraud_limit2, fraud_limit2, fraud_deductible1, fraud_deductible1, fraud_deductible2, fraud_deductible2, fraud_fulltermamt, fraud_fulltermamt, h051st0_limit1, h051st0_limit1, h051st0_limit2, h051st0_limit2, h051st0_deductible1, h051st0_deductible1, h051st0_deductible2, h051st0_deductible2, h051st0_fulltermamt, h051st0_fulltermamt, ho5_limit1, ho5_limit1, ho5_limit2, ho5_limit2, ho5_deductible1, ho5_deductible1, ho5_deductible2, ho5_deductible2, ho5_fulltermamt, ho5_fulltermamt, incb_limit1, incb_limit1, incb_limit2, incb_limit2, incb_deductible1, incb_deductible1, incb_deductible2, incb_deductible2, incb_fulltermamt, incb_fulltermamt, incc_limit1, incc_limit1, incc_limit2, incc_limit2, incc_deductible1, incc_deductible1, incc_deductible2, incc_deductible2, incc_fulltermamt, incc_fulltermamt, lac_limit1, lac_limit1, lac_limit2, lac_limit2, lac_deductible1, lac_deductible1, lac_deductible2, lac_deductible2, lac_fulltermamt, lac_fulltermamt, medpay_limit1, medpay_limit1, medpay_limit2, medpay_limit2, medpay_deductible1, medpay_deductible1, medpay_deductible2, medpay_deductible2, medpay_fulltermamt, medpay_fulltermamt, occupationdiscount_limit1, occupationdiscount_limit1, occupationdiscount_limit2, occupationdiscount_limit2, occupationdiscount_deductible1, occupationdiscount_deductible1, occupationdiscount_deductible2, occupationdiscount_deductible2, occupationdiscount_fulltermamt, occupationdiscount_fulltermamt, olt_limit1, olt_limit1, olt_limit2, olt_limit2, olt_deductible1, olt_deductible1, olt_deductible2, olt_deductible2, olt_fulltermamt, olt_fulltermamt, pihom_limit1, pihom_limit1, pihom_limit2, pihom_limit2, pihom_deductible1, pihom_deductible1, pihom_deductible2, pihom_deductible2, pihom_fulltermamt, pihom_fulltermamt, pprep_limit1, pprep_limit1, pprep_limit2, pprep_limit2, pprep_deductible1, pprep_deductible1, pprep_deductible2, pprep_deductible2, pprep_fulltermamt, pprep_fulltermamt, prtdvc_limit1, prtdvc_limit1, prtdvc_limit2, prtdvc_limit2, prtdvc_deductible1, prtdvc_deductible1, prtdvc_deductible2, prtdvc_deductible2, prtdvc_fulltermamt, prtdvc_fulltermamt, seniordiscount_limit1, seniordiscount_limit1, seniordiscount_limit2, seniordiscount_limit2, seniordiscount_deductible1, seniordiscount_deductible1, seniordiscount_deductible2, seniordiscount_deductible2, seniordiscount_fulltermamt, seniordiscount_fulltermamt, sewer_limit1, sewer_limit1, sewer_limit2, sewer_limit2, sewer_deductible1, sewer_deductible1, sewer_deductible2, sewer_deductible2, sewer_fulltermamt, sewer_fulltermamt, spp_limit1, spp_limit1, spp_limit2, spp_limit2, spp_deductible1, spp_deductible1, spp_deductible2, spp_deductible2, spp_fulltermamt, spp_fulltermamt, srorp_limit1, srorp_limit1, srorp_limit2, srorp_limit2, srorp_deductible1, srorp_deductible1, srorp_deductible2, srorp_deductible2, srorp_fulltermamt, srorp_fulltermamt, thefa_limit1, thefa_limit1, thefa_limit2, thefa_limit2, thefa_deductible1, thefa_deductible1, thefa_deductible2, thefa_deductible2, thefa_fulltermamt, thefa_fulltermamt, utldb_limit1, utldb_limit1, utldb_limit2, utldb_limit2, utldb_deductible1, utldb_deductible1, utldb_deductible2, utldb_deductible2, utldb_fulltermamt, utldb_fulltermamt, wcinc_limit1, wcinc_limit1, wcinc_limit2, wcinc_limit2, wcinc_deductible1, wcinc_deductible1, wcinc_deductible2, wcinc_deductible2, wcinc_fulltermamt, wcinc_fulltermamt, wcinc_limit1_o, wcinc_limit1_o, wcinc_limit2_o, wcinc_limit2_o, wcinc_deductible1_o, wcinc_deductible1_o, wcinc_deductible2_o, wcinc_deductible2_o, wcinc_fulltermamt_o, wcinc_fulltermamt_o |
Unique Keys | coveredrisk_id, systemid |
Update Strategy | Update/Insert |
Schema | ${schema_DW} |
Target Table Name | dim_policytransactionextension |
Fix Data Type Mismatches | No |
Column Mapping | loaddate, loaddate, policy_uniqueid, policy_id, systemid, systemid, bookdt, bookdt, transactioneffectivedt, transactioneffectivedt, policy_uniqueid, policy_uniqueid, policytransaction_uniqueid, policytransaction_uniqueid, transactionnumber, transactionnumber, transactioncd, transactioncd, transactionlongdescription, transactionlongdescription, transactionshortdescription, transactionshortdescription, canceltypecd, canceltypecd, cancelrequestedbycd, cancelrequestedbycd, cancelreason, cancelreason |
Unique Keys | policytransaction_uniqueid |
Update Strategy | Update/Insert |
Schema | ${schema_DW} |
Target Table Name | dim_customer |
Fix Data Type Mismatches | No |
Column Mapping | customer_id, customer_id, loaddate, loaddate, customer_uniqueid, customer_uniqueid, status, status, entitytypecd, entitytypecd, first_name, first_name, last_name, last_name, commercialname, commercialname, dob, dob, gender, gender, maritalstatus, maritalstatus, address1, address1, address2, address2, county, county, city, city, state, state, postalcode, postalcode, phone, phone, mobile, mobile, email, email, preferreddeliverymethod, preferreddeliverymethod, portalinvitationsentdt, portalinvitationsentdt, paymentreminderind, paymentreminderind, changedate, changedate |
Unique Keys | customer_uniqueid |
Update Strategy | Update/Insert |
SQL Query | with data as (select distinct addr1 address1 ,addr2 address2 ,county ,city ,stateprovcd state ,postalcode from ${schema_STG}.stg_building union select distinct addr1 address1 ,addr2 address2 ,county ,city ,stateprovcd state ,postalcode from ${schema_STG}.stg_vehicle ) select to_date('${loaddate}','yyyy-mm-dd') loaddate, * from data |
Schema | ${schema_DW} |
Target Table Name | dim_address |
Fix Data Type Mismatches | No |
Column Mapping | loaddate, loaddate, address1, address1, address2, address2, county, county, city, city, state, state, postalcode, postalcode |
Unique Keys | address1, address2, county, city, state, postalcode |
Update Strategy | Update/Insert |
SQL Query | select DISTINCT COV_LIMIT1 , COV_LIMIT2 , isnull(cast(case when COV_LIMIT1 !~ ('[^.0-9\-]') then COV_LIMIT1 else public.removenotnumeric(COV_LIMIT1) end as float),0) COV_LIMIT1_VALUE , isnull(cast(case when COV_LIMIT2 !~ ('[^.0-9\-]') then COV_LIMIT2 else public.removenotnumeric(COV_LIMIT2) end as float),0) COV_LIMIT2_VALUE , LOADDATE from ${schema_STG}.stg_policytransaction |
SQL Query | select DISTINCT ltrim(rtrim(COV_CLASSCODE)) CLASS_CODE , case when charindex('-',COV_CLASSCODE)-1>0 then ltrim(rtrim(left(COV_CLASSCODE,charindex('-',COV_CLASSCODE)-1))) else '~' end CLASS_CODENAME , case when charindex('-',COV_CLASSCODE)-1>0 then ltrim(rtrim(right(COV_CLASSCODE,len(COV_CLASSCODE) - charindex('-',COV_CLASSCODE)))) else '~' end CLASS_CODEDESCRIPTION , LOADDATE from ${schema_STG}.stg_policytransaction |
Schema | ${schema_DW} |
Target Table Name | dim_classification |
Fix Data Type Mismatches | No |
Column Mapping | loaddate, loaddate, class_code, class_code, class_codename, class_codename, class_codedescription, class_codedescription |
Unique Keys | class_code, class_codename, class_codedescription |
Update Strategy | Update/Insert |
SQL Query | select DISTINCT COV_DEDUCTIBLE1, COV_DEDUCTIBLE2, isnull(cast(case when COV_DEDUCTIBLE1 !~ ('[^.0-9\-]') then COV_DEDUCTIBLE1 else public.removenotnumeric(COV_DEDUCTIBLE1) end as float),0) COV_DEDUCTIBLE1_value , isnull(cast(case when COV_DEDUCTIBLE2 !~ ('[^.0-9\-]') then COV_DEDUCTIBLE2 else public.removenotnumeric(COV_DEDUCTIBLE2) end as float),0) COV_DEDUCTIBLE2_value , LOADDATE from ${schema_STG}.stg_policytransaction |
Schema | ${schema_DW} |
Target Table Name | dim_deductible |
Fix Data Type Mismatches | No |
Column Mapping | loaddate, loaddate, cov_deductible1, cov_deductible1, cov_deductible2, cov_deductible2, cov_deductible1_value, cov_deductible1_value, cov_deductible2_value, cov_deductible2_value |
Unique Keys | cov_deductible1, cov_deductible2 |
Update Strategy | Update/Insert |